﻿Imports System.IO
Imports System.Data.SqlClient
Imports System.Data.OleDb
Imports System.Data.Common
' mysql-connector-net-6.8.3.msi
Imports MySql.Data.MySqlClient

Public Class DataBaseLibrary

    '~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~
    '
    'This code is about Connection with Access DataBase

    Public Shared Sub getCountResultFromDataBase(ByVal SQLstring As String, ByRef result As Integer)

        Try
            Using cnn As New MySql.Data.MySqlClient.MySqlConnection(My.Settings.MySQLConnectionString)
                Using cmd As New MySqlCommand(SQLstring, cnn)
                    cnn.Open()
                    Using dr As MySqlDataReader = cmd.ExecuteReader()

                        'Loop through all the rows, retrieving the 
                        'columns you need.
                        While dr.Read
                            result = dr("C").ToString
                        End While
                    End Using
                    cnn.Close()
                End Using
            End Using
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try


    End Sub


    Public Shared Sub addInfoUserControlsFromDataBase(ByVal SQLstring As String, ByVal result As List(Of UserControlInterface))

        Try
            Using cnn As New MySql.Data.MySqlClient.MySqlConnection(My.Settings.MySQLConnectionString)
                Using cmd As New MySqlCommand(SQLstring, cnn)
                    cnn.Open()
                    Using dr As MySqlDataReader = cmd.ExecuteReader()

                        'Loop through all the rows, retrieving the 
                        'columns you need.
                        Dim i As Integer = 0
                        While dr.Read
                            result(i).addToSpecificUserControlInterfaceInfo(dr("adEventID").ToString, i)
                            i = i + 1
                        End While

                    End Using
                    cnn.Close()
                End Using
            End Using

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try


    End Sub
    Public Shared Sub updateUserControlsFromDataBase(ByVal SQLstring As String)

        Try
            Using cnn As New MySql.Data.MySqlClient.MySqlConnection(My.Settings.MySQLConnectionString)
                Using cmd As New MySqlCommand(SQLstring, cnn)

                    cnn.Open()
                    cmd.ExecuteNonQuery()
                    cnn.Close()

                End Using
            End Using

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
        'For mouse wheel
        Form1.PanelForAll.Select()
    End Sub

    Public Shared Sub setTheDataFomDataBase(ByVal SQLstring As String, ByVal This As UserControlInterface)

        Try
            Using cnn As New MySql.Data.MySqlClient.MySqlConnection(My.Settings.MySQLConnectionString)
                Using cmd As New MySqlCommand(SQLstring, cnn)
                    cnn.Open()
                    Using dr As MySqlDataReader = cmd.ExecuteReader()

                        ' Loop through all the rows, retrieving the 
                        ' columns you need. 

                        While dr.Read

                            This.setAdvertEventID(dr("adEventID").ToString)
                            This.setSiteEventID(dr("eventSiteID").ToString)
                            This.setEventAdID(dr("eventAdID").ToString)
                            This.setTypeEventID(dr("eventTypeID").ToString)
                            This.setUserEventID(dr("eventUserID").ToString)
                            This.setAdvertiserEventID(dr("eventAdvertiserID").ToString)
                            This.setRecordingID(dr("RecordingID").ToString)

                            This.setEventSiteValue(dr("SiteName").ToString)
                            This.setEventAdValue(dr("AdName").ToString)
                            This.setEventTypeValue(dr("TypeName").ToString)
                            This.setEventUserValue(dr("UserName").ToString)
                            This.setEventAdvertiserValue(dr("AdvertiserName").ToString)
                            This.setRecordingValue(dr("RecordingName").ToString)

                            This.setEventValue(dr("eventValue").ToString)
                            This.setEventDateValue(dr("EventDate").ToString)

                        End While

                    End Using
                    cnn.Close()
                End Using
            End Using

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

    End Sub

    Public Shared Sub setTheDataFomDataBase(ByVal SQLstring As String, ByVal result As List(Of UserControlReferences))

        Try
            Using cnn As New MySql.Data.MySqlClient.MySqlConnection(My.Settings.MySQLConnectionString)
                Using cmd As New MySqlCommand(SQLstring, cnn)
                    cnn.Open()
                    Using dr As MySqlDataReader = cmd.ExecuteReader()

                        ' Loop through all the rows, retrieving the 
                        ' columns you need. 

                        Dim i As Integer = 0
                        While dr.Read

                            result(i).setAdvertEventID(dr("adEventID").ToString)
                            result(i).setSiteEventID(dr("eventSiteID").ToString)
                            result(i).setEventAdID(dr("eventAdID").ToString)
                            result(i).setTypeEventID(dr("eventTypeID").ToString)
                            result(i).setUserEventID(dr("eventUserID").ToString)
                            result(i).setAdvertiserEventID(dr("eventAdvertiserID").ToString)
                            result(i).setRecordingID(dr("RecordingID").ToString)

                            result(i).setEventSiteValue(dr("SiteName").ToString)
                            result(i).setEventAdValue(dr("AdName").ToString)
                            result(i).setEventTypeValue(dr("TypeName").ToString)
                            result(i).setEventUserValue(dr("UserName").ToString)
                            result(i).setEventAdvertiserValue(dr("AdvertiserName").ToString)

                            result(i).setRecordingValue(dr("RecordingName").ToString)

                            result(i).setEventValue(dr("eventValue").ToString)
                            result(i).setEventDateValue(dr("EventDate").ToString)
                            i = i + 1

                        End While

                    End Using
                    cnn.Close()
                End Using
            End Using

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

    End Sub

    Public Shared Function ConvertDateForThisDataBase(ByVal timeStamp As String)

        'This method is for specific database 
        Dim separators() As String = {"/", ":", " "}
        Dim words() As String = timeStamp.Split(separators, StringSplitOptions.RemoveEmptyEntries)
        Dim converted As String = "'" & words(2).ToString & "-" & words(1).ToString & "-" & words(0).ToString & " " & words(3).ToString & ":" & words(4).ToString & ":" & words(5).ToString & "'"
        Return converted.ToString

    End Function

    Public Shared Function RecordExists(ByVal databaseTable As String, ByVal columnID As String, ByVal columnName As String, ByVal member As String)
        Dim SQLstring = " SELECT COUNT(*) AS C FROM " & databaseTable & _
                        " WHERE " & columnName & " = '" & member & "';"
        Dim result As Integer
        Try
            Using cnn As New MySql.Data.MySqlClient.MySqlConnection(My.Settings.MySQLConnectionString)
                Dim count As Integer
                Using cmd As New MySqlCommand(SQLstring, cnn)
                    cnn.Open()

                    Using dr As MySqlDataReader = cmd.ExecuteReader()

                        ' Loop through all the rows, retrieving the 
                        ' columns you need. 
                        While dr.Read
                            count = dr("C").ToString
                        End While

                    End Using
                    cnn.Close()
                End Using
                If count = 0 Then
                    result = 0
                Else
                    SQLstring = " SELECT " & columnID & " FROM " & databaseTable & _
                                " WHERE " & columnName & " = '" & member & "';"
                    Using cmd As New MySqlCommand(SQLstring, cnn)
                        cnn.Open()
                        Using dr As MySqlDataReader = cmd.ExecuteReader()

                            ' Loop through all the rows, retrieving the 
                            ' columns you need. 
                            While dr.Read
                                result = dr(columnID)
                            End While

                        End Using
                        cnn.Close()
                    End Using
                End If
            End Using

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
        Return result
    End Function

    Public Shared Function IdRecordExists(ByVal ID As Integer, ByVal databaseTable As String, ByVal columnID As String)
        Dim SQLstring = " SELECT " & columnID & " FROM " & databaseTable & " ;"

        Dim result As Boolean = False
        Try
            Using cnn As New MySql.Data.MySqlClient.MySqlConnection(My.Settings.MySQLConnectionString)

                Using cmd As New MySqlCommand(SQLstring, cnn)
                    cnn.Open()
                    Using dr As MySqlDataReader = cmd.ExecuteReader()

                        ' Loop through all the rows, retrieving the 
                        ' columns you need. 
                        While dr.Read
                            If ID = dr(columnID) Then
                                result = True
                            End If
                        End While

                    End Using
                    cnn.Close()
                End Using
            End Using

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
        Return result
    End Function


End Class
